1. Field of the Invention
The present invention relates to the field of programmatic database access and more particularly to the field of programmatic database row updating utilizing a proxy driver.
2. Description of the Related Art
Database access middleware provides a stylized connection between an application computer program and a database. By stylized connection, it is meant that a database link has a formal, published definition. This formal, published definition can identify the interface through which connected application programs can issue data access requests and, in response thereto, receive database content through the database link. Two contemporary examples of database access middleware include the Open Database Connectivity (ODBC) and JDBC™ (Sun Microsystems, Inc.) specifications. ODBC and JDBC are important connectivity technologies because presently, ODBC and JDBC have been implemented in a bevy of disparate platforms while providing a common interface to several different database servers.
Primarily, database access middleware shuttles data requests and database content to and from application programs. In addition, database access middleware helps to ensure security, and it insulates the application from having to interact directly with the database server. Generalized query tools can utilize database access middleware (rather than the database server itself) to provide query services for multiple types of database products. Also, computer programs written according to Visual Basic, C, C++, Pascal, COBOL and other programming languages can perform database operations via ODBC. By comparison, programs based upon Java™ (Sun Microsystems, Inc.) technology can use JDBC to perform database operations.
JDBC technology is an application programming interface (API) that permits application programs to access virtually any tabular data source using the Java programming language. In consequence, JDBC technology provides cross database management system (DBMS) connectivity to a wide range of databases, and other tabular data sources, such as spreadsheets or flat files. With a JDBC enabled driver, a developer can easily connect all corporate data even in a heterogeneous environment.
The “Prepared Statement” is the preferred mechanism for accessing a relational database in JDBC. One often utilizes the PreparedStatement object when a structured query language (SQL) statement is to be run many times. It is well known that a SQL statement can be precompiled, and a Prepared Statement is a SQL statement that has been precompiled. Thus, the Prepared Statement approach can be more efficient than executing the same statement multiple times using a JDBC Statement object, which compiles the statement for each execution of the statement.
Notably, a Prepared Statement is the Java encapsulation of a parameterized query in which the SQL statement compiles a single time, but can execute many times. To change the query conditions, placeholders are utilized within the statement to indicate bind variables. An example follows:
PreparedStatement ps = con.prepareStatement(“UPDATE row SET col1 = ?, set col2 = ? ... set colN = ?WHERE key = ?”)In this statement, columns 1 through N are bound to variables denoted by the question marks. These variables can be completed in program code prior to calling the execution of the query. Importantly, every field in the statement must be specified prior to performing the query execution. Not one field can be omitted.
Accordingly, if only a portion of the row is to be updated, the reuse of the Prepared Statement can be limited in that different Prepared Statements must be generated for each combination of fields in a row to be updated. To generate a Prepared Statement for each combination of fields in a row to be updated, however, can exact a significant performance penalty due to SQL statement parsing and pre-compilation. First, it has been estimated that a total number of Prepared Statement combinations for updating a row of N fields excluding the primary key field is (2n−1). Thus, to cache so many possible Prepared Statements can impact memory to the tune of (2n−1) cache entries. Second, the parsing process must occur potentially (2n−1) times thereby incurring performance overhead in the SQL engine.